USE [QLTHUCPHAM]
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectMonAnAll]    Script Date: 09/27/2013 20:20:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_CreateChiTietPhieuDatId







/* ------------------- Mon An---------------------*/

/* Select Mon An - Nguyen Thi Yen */
create proc [dbo].[sp_SelectMonAnAll] @tongmonan int out
as
	begin
		set @tongmonan = (select count(Ma_Mon)
								from MonAn)
		--waitfor delay '0:0:05'
		select ma.Ma_Mon,ma.TenMon,ma.Loai, ma.GiaTien
		from MonAn ma
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectMonAnById]    Script Date: 09/27/2013 20:20:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Select Mon An By Id - Nguyen Thi Yen */
Create proc [dbo].[sp_SelectMonAnById] @maMon nchar(10)
as
	begin
		select ma.Ma_Mon,ma.TenMon,ma.Loai, ma.GiaTien
		from MonAn ma
		where ma.Ma_Mon =@maMon
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertMonAn]    Script Date: 09/27/2013 20:20:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Insert Mon An - Nguyen Thi Yen */
create proc sp_InsertMonAn
@maMon nchar(10),
	@tenMon	nvarchar(50),
	@loai nchar(10),
	@giaTien float
as
begin tran
	SET TRAN ISOLATION LEVEL SERIALIZABLE
	declare @SL int
	select @SL =count(*) from MonAn  where TenMon = @tenMon 

	waitfor delay '0:0:03'
	if (@SL = 0)
		Begin
			Insert into MonAn(Ma_Mon,TenMon,Loai, GiaTien)
			Values (@maMon ,@tenMon	,@loai,@giaTien)
		End
commit tran
go
/****** Object:  StoredProcedure [dbo].[sp_UpdateMonAnById]    Script Date: 09/27/2013 20:20:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Update Mon An - Nguyen Thi Yen */
Create proc [dbo].[sp_UpdateMonAnById]
	@maMon nchar(10),
	@tenMon	nvarchar(50),
	@loai nchar(10),
	@giaTien float
as
	begin
		Update MonAn
		set TenMon=@tenMon,Loai=@loai,GiaTien=@giaTien
		where Ma_Mon=@maMon
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_DeleteMonAnById]    Script Date: 09/27/2013 20:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Delete Mon An - Nguyen Thi Yen */
create proc [dbo].[sp_DeleteMonAnById] @maMon nchar(10)
as
	begin	
		delete from MonAn 
		where Ma_Mon = @maMon
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_CreateMonAnId]    Script Date: 09/27/2013 20:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Create ID Mon An - Nguyen Thi Yen */
create proc [dbo].[sp_CreateMonAnId]
as
	begin
		declare @max nchar(10)
		select @max = max(Ma_Mon) from MonAn
		if(@max is NULL)
			select '001'
		else
			begin
				declare @id nchar(3)
				declare @so int
				SET @so =  cast(@max AS INT)+1

				if(@so<10)
					set @id='00'+ cast(@so AS NCHAR(1))
				else if(@so<100)
					set @id = '0'+ cast(@so AS NCHAR(2))
				
				SELECT @id		
			end
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectChiTietPhieuGiaoAll]    Script Date: 09/27/2013 20:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ------------- Chi Tiet Phieu Giao ---------------------*/

/* Select Chi Tiet Phieu Giao - Nguyen Thi Yen */
create proc [dbo].[sp_SelectChiTietPhieuGiaoAll]
as
	begin
		select MaChiTietPG, Ma_PG,Ma_Mon,SoLuong,DonGia,SoHop
		from ChiTietPhieuGiao
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectChiTietPhieuGiaoById]    Script Date: 09/27/2013 20:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Select Chi Tiet Phieu Giao By Id - Nguyen Thi Yen */
create proc [dbo].[sp_SelectChiTietPhieuGiaoById] @maChiTietPG nchar(10)
as
	begin
		select MaChiTietPG, Ma_PG,Ma_Mon,SoLuong,DonGia,SoHop
		from ChiTietPhieuGiao
		where MaChiTietPG = @maChiTietPG
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertChiTietPhieuGiao]    Script Date: 09/27/2013 20:20:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Insert Chi Tiet Phieu Giao - Nguyen Thi Yen */
create proc [dbo].[sp_InsertChiTietPhieuGiao]
	@maChiTietPG nchar(10),
	@ma_PG nchar(10),
	@ma_Mon nchar(10),
	@soLuong int,
	@donGia float,
	@soHop int
as
	begin
		Insert into ChiTietPhieuGiao(MaChiTietPG, Ma_PG,Ma_Mon,SoLuong,DonGia,SoHop)
		Values (@maChiTietPG,@ma_PG,@ma_Mon,@soLuong,@donGia,@soHop)
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_DeleteChiTietPhieuGiaoById]    Script Date: 09/27/2013 20:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Update Chi Tiet Phieu Giao - Nguyen Thi Yen */
--khong cho update
/*create proc sp_UpdateChiTietPhieuGiaoById
	@maChiTietPG nchar(10),
	@ma_PG nchar(10),
	@ma_Mon nchar(10),
	@soLuong int,
	@donGia float,
	@soHop int
as
	begin
		Update ChiTietPhieuGiao
		set Ma_PG=@ma_PG,Ma_Mon=@ma_Mon,SoLuong=@soLuong,DonGia=@donGia,SoHop=@soHop
		where MaChiTietPG=@maChiTietPG
	end
go*/

/* Delete Chi Tiet Phieu Giao - Nguyen Thi Yen */
create proc [dbo].[sp_DeleteChiTietPhieuGiaoById] @maChiTietPG nchar(10)
as
	begin
		delete from ChiTietPhieuGiao
		where MaChiTietPG=@maChiTietPG
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_CreateChiTietPhieuGiaoId]    Script Date: 09/27/2013 20:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Create ID Chi Tiet Phieu Giao - Nguyen Thi Yen */
CREATE proc [dbo].[sp_CreateChiTietPhieuGiaoId]
as
	begin
		declare @max nchar(10)
		select @max = max(MaChiTietPG) from ChiTietPhieuGiao
		if(@max is NULL)
			select '001'
		else
			begin
				declare @id nchar(3)
				declare @so int
				SET @so =  cast(@max AS INT)+1

				if(@so<10)
					set @id='00'+ cast(@so AS NCHAR(1))
				else if(@so<100)
					set @id = '0'+ cast(@so AS NCHAR(2))
				
				SELECT @id	
			end
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertKhuyenMai]    Script Date: 09/27/2013 20:20:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--////////////////////////////////////////KHUYEN MAI//////////////////////////////////////////
CREATE PROCEDURE [dbo].[sp_InsertKhuyenMai]
	@Ma_KhuyenMai nchar (3),
	@Ma_MonAn nchar (3),
	@PhanTramGiam int,
	@NgayBatDau datetime,
	@NgayKetThuc datetime
AS
BEGIN TRAN
	DECLARE  @NBD datetime
	DECLARE @NKT datetime
	DECLARE @cur CURSOR
	DECLARE @FLAG int
	SET @FLAG=0
	SET @cur= CURSOR FOR (SELECT NgayBatDau,NgayKetThuc FROM KhuyenMai WHERE Ma_MonAn=@Ma_MonAn)
	OPEN @cur
	FETCH NEXT FROM @cur INTO @NBD,@NKT
	WHILE @@fetch_status = 0
	BEGIN
		IF((@NgayBatDau between @NBD and @NKT)OR (@NgayKetThuc between @NBD and @NKT) OR ((@NBD between @NgayBatDau and @NgayKetThuc) and (@NKT between @NgayBatDau and @NgayKetThuc)) )
		BEGIN
				SET @FLAG=1
		END
		
		FETCH NEXT FROM @cur INTO @NBD,@NKT
	END
	
	
	IF(@FLAG=0)
	BEGIN
		INSERT INTO KhuyenMai VALUES(@Ma_KhuyenMai,@Ma_MonAn,@PhanTramGiam,@NgayBatDau,@NgayKetThuc)
	END
	close @cur
	deallocate @cur
COMMIT TRAN
GO
/****** Object:  StoredProcedure [dbo].[sp_UpdateKhuyenMaiById]    Script Date: 09/27/2013 20:20:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----EXEC sp_InsertKhuyenMai '015','004',10,'2013-07-03 00:00:00.000','2013-07-09 00:00:00.000'


--Update Thông tin khuyễn mãi
CREATE PROCEDURE [dbo].[sp_UpdateKhuyenMaiById]
	@Ma_KhuyenMai nchar (3),
	@Ma_MonAn nchar (3),
	@PhanTramGiam int,
	@NgayBatDau datetime,
	@NgayKetThuc datetime
AS
BEGIN
BEGIN TRAN
	Update KhuyenMai SET Ma_MonAn=@Ma_MonAn,PhanTramGiam=@PhanTramGiam,NgayKetThuc=@NgayKetThuc,NgayBatDau=@NgayBatDau WHERE Ma_KhuyenMai=@Ma_KhuyenMai
COMMIT TRAN
END
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectKhuyenMaiAll]    Script Date: 09/27/2013 20:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_UpdateKhuyenMaiById '001','005',15,'2013-08-30 00:00:00.000','2013-010-30 00:00:00.000'



CREATE PROCEDURE [dbo].[sp_SelectKhuyenMaiAll] @soKM int out
AS
BEGIN
BEGIN TRAN
	
	set @soKM = (select count(*) from KhuyenMai)

	--waitfor delay '0:0:03'
	SELECT Ma_KhuyenMai,Ma_MonAn,PhanTramGiam,NgayBatDau,NgayKetThuc FROM KhuyenMai
	
COMMIT TRAN
END
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectKhuyenMaiById]    Script Date: 09/27/2013 20:20:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_SelectKhuyenMaiAll






CREATE PROCEDURE [dbo].[sp_SelectKhuyenMaiById]
	@Ma_KhuyenMai nchar (3)
AS
BEGIN
BEGIN TRAN
	SELECT Ma_MonAn,PhanTramGiam,NgayBatDau,NgayKetThuc FROM KhuyenMai WHERE Ma_KhuyenMai=@Ma_KhuyenMai
COMMIT TRAN
END
GO
/****** Object:  StoredProcedure [dbo].[sp_DeleteKhuyenMaiById]    Script Date: 09/27/2013 20:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_SelectKhuyenMaiById '002'


create proc sp_SelectKhuyenMaiByMaMonAn
	@Ma_MonAn nchar(3),
	@soKM int out

as
begin tran
	--set tran isolation level repeatable read  /*Demo Unrepeatable read 1*/
	--set tran isolation level serializable /*Demo PhanTom 1*/
	set @soKM = (select count(*) from KhuyenMai WHERE Ma_MonAn=@Ma_MonAn)

	waitfor delay '0:0:03'
	SELECT Ma_KhuyenMai,Ma_MonAn,PhanTramGiam,NgayBatDau,NgayKetThuc FROM KhuyenMai WHERE Ma_MonAn=@Ma_MonAn
commit tran
go


CREATE PROCEDURE [dbo].[sp_DeleteKhuyenMaiById]
	@Ma_KhuyenMai nchar (3)
AS
BEGIN
BEGIN TRAN
	DELETE FROM KhuyenMai WHERE Ma_KhuyenMai=@Ma_KhuyenMai
COMMIT TRAN
END
GO
/****** Object:  StoredProcedure [dbo].[sp_CreateKhuyenMaiId]    Script Date: 09/27/2013 20:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_CreateKhuyenMaiId]
AS
BEGIN
		DECLARE @max NCHAR(10)
		
		SELECT @max = max(Ma_KhuyenMai) FROM KhuyenMai
		IF(@max is NULL)
			SELECT '001'
		ELSE
			BEGIN
				declare @id nchar(3)
				declare @so int
				SET @so =  cast(@max AS INT)+1

				if(@so<10)
					set @id='00'+ cast(@so AS NCHAR(1))
				else if(@so<100)
					set @id = '0'+ cast(@so AS NCHAR(2))
				
				SELECT @id
			END
END
GO
/****** Object:  StoredProcedure [dbo].[sp_UpdateThucPhamSoCheById]    Script Date: 09/27/2013 20:20:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_UpdateThucPhamSoCheById]
	@Ma_PD nchar(3)
	
AS
BEGIN TRAN
	DECLARE @MaMon nchar(3)
	DECLARE @SL int
	DECLARE @SoPhan int
	DECLARE @cur CURSOR
	SET @cur= CURSOR FOR (SELECT Ma_Mon,SoLuong FROM ChiTietPhieuDat WHERE Ma_PD=@Ma_PD)
	OPEN @cur
	FETCH NEXT FROM @cur INTO @MaMon,@SL
	WHILE @@fetch_status = 0
	BEGIN
		--SELECT @MaMon AS N'Mã món ăn',@SL AS N'Số lượng'
		SET @SoPhan=0
		SELECT @SoPhan=SoPhan FROM ThucPhamSoChe WHERE Ma_Mon=@MaMon
		IF(@SoPhan>=@SL)
		BEGIN
			DECLARE @SoPhanConLai int
			SET @SoPhanConLai=@SoPhan-@SL			
			SELECT  @SoPhanConLai AS N'Số lượng còn lại'
			WAITFOR DELAY '00:00:03'
			UPDATE ThucPhamSoChe SET SoPhan=@SoPhanConLai WHERE Ma_Mon=@MaMon

		END
		ELSE
		BEGIN
			ROLLBACK TRAN
			RETURN
		END	
		FETCH NEXT FROM @cur INTO @MaMon,@SL	
	END
	close @cur
	deallocate @cur
COMMIT TRAN

--EXEC sp_UpdateThucPhamSoCheById '277'
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertChiTietPhieuDat]    Script Date: 09/27/2013 20:20:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_CreatePhieuDatId


--/////////////////////////////////////////////////////////////CHI TIET PHIEU DAT////////////////////////////////////////////

CREATE PROCEDURE [dbo].[sp_InsertChiTietPhieuDat]
	@MaChiTietPD nchar(3),
	@Ma_PD nchar(3),
	@Ma_Mon nchar(3),
	@SoLuong int,
	@DonGia float,
	@ThanhTien float,
	@GhiChu nvarchar(100)
AS
BEGIN
BEGIN TRAN
	INSERT INTO ChiTietPhieuDat VALUES(@MaChiTietPD,@Ma_PD,@Ma_Mon,@SoLuong,@DonGia,@ThanhTien,@GhiChu)
COMMIT TRAN
END
GO
/****** Object:  StoredProcedure [dbo].[sp_UpdateChiTietPhieuDatById]    Script Date: 09/27/2013 20:20:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_InsertChiTietPhieuDat'023','010','013',5,27000,135000,null

CREATE PROCEDURE [dbo].[sp_UpdateChiTietPhieuDatById]
	@MaChiTietPD nchar(3),
	@Ma_PD nchar(3),
	@Ma_Mon nchar(3),
	@SoLuong int,
	@DonGia float,
	@ThanhTien float,
	@GhiChu nvarchar(100)
AS
BEGIN
BEGIN TRAN
	Update ChiTietPhieuDat SET Ma_PD=@Ma_PD,Ma_Mon=@Ma_Mon,SoLuong=@SoLuong,DonGia=@DonGia,ThanhTien=@ThanhTien,GhiChu=@GhiChu WHERE MaChiTietPD=@MaChiTietPD
COMMIT TRAN
END
GO
/****** Object:  StoredProcedure [dbo].[sp_DeleteChiTietPhieuDatById]    Script Date: 09/27/2013 20:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_UpdateChiTietPhieuDatById'023','010','013',5,2700000,135000,null

CREATE PROCEDURE [dbo].[sp_DeleteChiTietPhieuDatById]
	@MaChiTietPD nchar(3)
AS
BEGIN
BEGIN TRAN
	DELETE FROM ChiTietPhieuDat WHERE MaChiTietPD=@MaChiTietPD
COMMIT TRAN
END
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectChiTietPhieuDatAll]    Script Date: 09/27/2013 20:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_DeleteChiTietPhieuDatById '023'


CREATE PROCEDURE [dbo].[sp_SelectChiTietPhieuDatAll]
AS
BEGIN
BEGIN TRAN
	SELECT MaChiTietPD,Ma_PD,Ma_Mon,SoLuong,DonGia,ThanhTien,GhiChu FROM ChiTietPhieuDat
COMMIT TRAN
END
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectChiTietPhieuDatById]    Script Date: 09/27/2013 20:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_SelectChiTietPhieuDatAll

CREATE PROCEDURE [dbo].[sp_SelectChiTietPhieuDatById]
	@MaChiTietPD nchar(3)
AS
BEGIN
BEGIN TRAN
	SELECT Ma_PD,Ma_Mon,SoLuong,DonGia,ThanhTien,GhiChu FROM ChiTietPhieuDat WHERE MaChiTietPD=@MaChiTietPD
COMMIT TRAN
END
GO
/****** Object:  StoredProcedure [dbo].[sp_CreateChiTietPhieuDatId]    Script Date: 09/27/2013 20:20:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[sp_CreateChiTietPhieuDatId]
AS
BEGIN
		DECLARE @max NCHAR(10)
		SELECT @max = max(MaChiTietPD) FROM ChiTietPhieuDat
		IF(@max is NULL)
			SELECT '001'
		ELSE
			BEGIN
				declare @id nchar(3)
				declare @so int
				SET @so =  cast(@max AS INT)+1

				if(@so<10)
					set @id='00'+ cast(@so AS NCHAR(1))
				else if(@so<100)
					set @id = '0'+ cast(@so AS NCHAR(2))
				
				SELECT @id
			END
END
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectNhanVienById]    Script Date: 09/27/2013 20:20:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[sp_SelectNhanVienById] @MaNhanVien nchar(10)
as
	begin
				select nv.Ma_NV, nv.Password, nv.Ten_NV, nv.NgaySinh, nv.Luong, nv.Loai_NV
				from NhanVien nv
				where nv.Ma_NV = @MaNhanVien
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_UpdatePhieuDatById]    Script Date: 09/27/2013 20:20:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_InsertPhieuDat '011','005',N'Lê Văn Lách Luât',N'9999 Hai Bà Trưng','37477755','2013-05-09 00:00:00.000',236000,1,N'Ưho knớ'

CREATE PROCEDURE [dbo].[sp_UpdatePhieuDatById]
	@Ma_PD nchar(3),
	@NV_Dat nchar(3),
	@Ten_KH nvarchar(50),
	@DiaChi nvarchar(100),
	@DienThoai nvarchar(15),
	@NgayDat datetime,
	@ThanhTien  float,
	@TinhTrang  int,
	@GhiChu nvarchar(100)
AS
BEGIN
BEGIN TRAN
	UPDATE  PhieuDat SET NV_Dat=@NV_Dat,Ten_KH=@Ten_KH,DiaChi=@DiaChi,DienThoai=@DienThoai,NgayDat=@NgayDat,ThanhTien=@ThanhTien,TinhTrang=@TinhTrang,GhiChu=@GhiChu WHERE Ma_PD=@Ma_PD
COMMIT TRAN
END
GO
/****** Object:  StoredProcedure [dbo].[sp_DeletePhieuDatById]    Script Date: 09/27/2013 20:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_UpdatePhieuDatById '011','005',N'Lê Văn Lách Luât',N'9999999 Hai Bà Trưng','37477755','2013-05-09 00:00:00.000',236000,1,N'Ưho knớ' 

CREATE PROCEDURE [dbo].[sp_DeletePhieuDatById]
	@Ma_PD nchar(3)
AS
BEGIN
BEGIN TRAN
	DELETE FROM PhieuDat WHERE Ma_PD=@Ma_PD
COMMIT TRAN
END
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectPhieuDatAll]    Script Date: 09/27/2013 20:20:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_DeletePhieuDatById '011'

CREATE PROCEDURE [dbo].[sp_SelectPhieuDatAll]
@TONGPHIEU INT OUTPUT
AS
BEGIN
BEGIN TRAN

	SELECT @TONGPHIEU = (SELECT COUNT(*) FROM PHIEUDAT)
	
	WAITFOR DELAY '00:00:15'

	SELECT  Ma_PD,NV_Dat,Ten_KH,DiaChi,DienThoai,NgayDat,ThanhTien,TinhTrang,GhiChu FROM PhieuDat
	
COMMIT TRAN
END
GO

/****** Object:  StoredProcedure [dbo].[sp_SelectPhieuDatById]    Script Date: 09/27/2013 20:20:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_SelectPhieuDat

CREATE PROCEDURE [dbo].[sp_SelectPhieuDatById]
	@Ma_PD nchar(3)
AS
BEGIN
BEGIN TRAN
	SELECT  NV_Dat,Ten_KH,DiaChi,DienThoai,NgayDat,ThanhTien,TinhTrang,GhiChu FROM PhieuDat WHERE Ma_PD=@Ma_PD
COMMIT TRAN
END
GO
/****** Object:  StoredProcedure [dbo].[sp_CreatePhieuDatId]    Script Date: 09/27/2013 20:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_SelectPhieuDatById'002'

CREATE PROCEDURE [dbo].[sp_CreatePhieuDatId]
AS
BEGIN
		DECLARE @max NCHAR(10)
		SELECT @max = max(Ma_PD) FROM PhieuDat
		IF(@max is NULL)
			SELECT '001'
		ELSE
			BEGIN
			declare @id nchar(3)
			declare @so int
			SET @so =  cast(@max AS INT)+1

			if(@so<10)
				set @id='00'+ cast(@so AS NCHAR(1))
			else if(@so<100)
				set @id = '0'+ cast(@so AS NCHAR(2))
			
				
			SELECT @id
			END
END
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertPhieuDat]    Script Date: 09/27/2013 20:20:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sp_CreateKhuyenMaiId


--////////////////////////////////////////////DAT HANG/////////////////////////////////////////
CREATE PROCEDURE [dbo].[sp_InsertPhieuDat]
	@Ma_PD nchar(3),
	@NV_Dat nchar(3),
	@Ten_KH nvarchar(50),
	@DiaChi nvarchar(100),
	@DienThoai nvarchar(15),
	@NgayDat datetime,
	@ThanhTien  float,
	@TinhTrang  int,
	@GhiChu nvarchar(100)
AS
BEGIN
BEGIN TRAN
	INSERT INTO PhieuDat  VALUES(@Ma_PD,@NV_Dat,@Ten_KH,@DiaChi,@DienThoai,@NgayDat,@ThanhTien,@TinhTrang,@GhiChu)
COMMIT TRAN
END

--EXEC sp_InsertPhieuDat '313', '001', 'a', 'a', 'a', '9/9/2013', 1, 1, 'a'
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectPhieuGiaoAll]    Script Date: 09/27/2013 20:20:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ------------------- Phieu Giao---------------------*/

/* Select Phieu Giao - Nguyen Thi Yen */
create proc [dbo].[sp_SelectPhieuGiaoAll]
as
	begin
		select pg.Ma_PG,pg.Ma_PD,pg.NV_Giao,pg.NgayGiao,pg.ThanhTien,pg.TinhTrang,pg.Ghi_Chu
		from PhieuGiao pg
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectPhieuGiaoById]    Script Date: 09/27/2013 20:20:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Select Phieu Giao By Id - Nguyen Thi Yen */
create proc [dbo].[sp_SelectPhieuGiaoById] @maPG nchar(10)
as
	begin
		select pg.Ma_PG,pg.Ma_PD,pg.NV_Giao,pg.NgayGiao,pg.ThanhTien,pg.TinhTrang,pg.Ghi_Chu
		from PhieuGiao pg
		where pg.Ma_PG = @maPG
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertPhieuGiao]    Script Date: 09/27/2013 20:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Insert Phieu Giao - Nguyen Thi Yen */
create proc [dbo].[sp_InsertPhieuGiao]
	@maPG nchar(10),
	@maPD nchar(10),
	@nVGiao nchar(10),
	@ngayGiao datetime,
	@thanhTien float,
	@tinhTrang int,
	@ghiChu	text
as
	begin
		Insert into PhieuGiao (Ma_PG,Ma_PD,NV_Giao,NgayGiao,ThanhTien,TinhTrang,Ghi_Chu)
		Values (@maPG,@maPD,@nVGiao,@ngayGiao,@thanhTien,@tinhTrang,@ghiChu)
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_DeletePhieuGiaoById]    Script Date: 09/27/2013 20:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Update Phieu Giao - Nguyen Thi Yen */
--khong cho update
/*create proc sp_UpdatePhieuGiao
	@maPG nchar(10),
	@maPD nchar(10),
	@nVGiao nchar(10),
	@ngayGiao datetime,
	@thanhTien float,
	@tinhTrang int,
	@ghiChu	text
as
	begin
		Update PhieuGiao 
		set Ma_PD =@maPD,NV_Giao =@nVGiao,NgayGiao =@ngayGiao,ThanhTien = @thanhTien,TinhTrang = @tinhTrang,Ghi_Chu = @ghiChu)
		where Ma_PG = @maPG
	end
go*/


/* Delete Phieu Giao - Nguyen Thi Yen */
create proc [dbo].[sp_DeletePhieuGiaoById] @maPG nchar(10)
as
	begin
		update PhieuGiao
		set TinhTrang=0 --TinhTrang="huy"
		where Ma_PG=@maPG
	end
GO
/****** Object:  StoredProcedure [dbo].[sp_CreatePhieuGiaoId]    Script Date: 09/27/2013 20:20:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Create ID Phieu Giao - Nguyen Thi Yen */
create proc [dbo].[sp_CreatePhieuGiaoId]
as
	begin
		declare @max nchar(10)
		select @max = max(Ma_PG) from PhieuGiao
		if(@max is NULL)
			select '001'
		else
			begin
				declare @id nchar(3)
				declare @so int
				SET @so =  cast(@max AS INT)+1

				if(@so<10)
					set @id='00'+ cast(@so AS NCHAR(1))
				else if(@so<100)
					set @id = '0'+ cast(@so AS NCHAR(2))
				
				SELECT @id		
			end
	end
GO
